/******************************************************************************
	This file is a part of the Snapshot Bioinformatics Project

	Copyright (C) 2012-2013 Jarom Schow

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
******************************************************************************/

#include "SeqDatabase.h"

#include <QtCore/QVariant>
#include <QtCore/QDate>
#include <QtCore/QDebug>
#include <QtSql/QSqlError>

#include "Alphabet.h"
#include "DbXrefInsertBuilder.h"
#include "Core.h"
#include "Progress.h"
#include "SeqFeature.h"
#include "SeqLocation.h"
#include "SeqParser.h"
#include "SeqRange.h"
#include "SeqRecord.h"
#include "Sequence.h"
#include "SqlInsertBuilder.h"
#include "SqlServer.h"
#include "Strings.h"

using namespace Bio;

const int	c_largeSequenceLimit = 0x00040000; // 256 kbytes
const int	c_maxTextSize = 0x0000ffff; // 64 kbytes - 1

/**
**/
class SeqDatabase::SeqDatabaseData : public QSharedData {
public:
	SeqDatabaseData();
	SeqDatabaseData( const QString &name, int bioID, SqlServer *server );
	~SeqDatabaseData();

	bool	isValid() const;

	SqlServer*	getServer() const;

	QString	getId() const;
	void	setId( const QString &id );

	QString	getName() const;
	void	setName( const QString &name );

	SeqRecord	getRecord( int bioentryId );
	SeqRecord	getRecordByAccession( const QString &accession );

	QList<SeqRecord>	getRecordsForTaxon( int taxonId );

	QString	getAnnotation( const QString &key ) const;
	void	setAnnotation( const QString &key, const QString &value );

	QMapIterator<QString, QString>	getAnnotationIterator() const;

	void	buildQuery( const QString &sql );
	bool	addValue( const QString &val );
	bool	addValue( int val );
	bool	exec();

	bool	load( SeqParser *parser );
	bool	load( SeqRecord &record );
	bool	loadAll( SeqParser *parser );

	bool	loadBioentryTables( SeqParser *parser );
	bool	loadBioentryTable( SeqRecord &record );
	QString	makeBioentryTableValues( const SeqRecord &record, int bioId = -1 );

	bool	loadBioentryDates( SeqParser *parser );
	bool	loadBioentryDate( const SeqRecord &record );
	QString	makeBioentryDateValues( const SeqRecord &record );

	bool	loadBiosequences( SeqParser *parser );
	bool	loadBiosequence( const SeqRecord &record );
	bool	loadBigBiosequence( Sequence *seq, int bioId );
	QString	makeBiosequenceValues( Sequence *seq, int bioId );

	bool	loadComments( SeqParser *parser );
	bool	loadComment( const SeqRecord &record );
	QString	makeCommentValues( const SeqRecord &record );

	bool	loadDbXRefs( SeqParser *parser, DbXrefInsertBuilder &dbXRefs );
	bool	loadDbXRefs( const SeqRecord &record, DbXrefInsertBuilder &dbXRefs );

	bool	loadReferences( SeqParser *parser );
	bool	loadReferences( const SeqRecord &record );

	bool	loadAnnotations( SeqParser *parser );
	bool	loadAnnotations( const SeqRecord &record );
	bool	loadAnnotations( const SeqRecord &record, SqlInsertBuilder &insert );

	bool	loadSeqFeatures( SeqParser *parser, DbXrefInsertBuilder &dbXRefs );
	bool	loadSeqFeatures( const SeqRecord &record, DbXrefInsertBuilder &dbXRefs );
	
	bool	loadSeqFeaturesBasic( SeqParser *parser );
	bool	loadSeqFeaturesBasic( const SeqRecord &record, SqlInsertBuilder &insert, int &seqfeatureId );
	bool	loadSeqFeatureBasic( SeqFeature *feature, int rank, int bioId );

	bool	loadSeqFeaturesLocations( SeqParser *parser, DbXrefInsertBuilder &dbXRefs );
	bool	loadSeqFeatureLocations( SeqFeature *feature, SqlInsertBuilder &insert, DbXrefInsertBuilder &dbXRefs );
	bool	loadSeqFeatureLocation( const SeqLocation &loc, SqlInsertBuilder &insert, DbXrefInsertBuilder &dbXRefs, int rank, int seqFeatureId );

	bool	loadSeqFeaturesQualifiers( SeqParser *parser, DbXrefInsertBuilder &dbXRefs );
	bool	loadSeqFeatureQualifiers( SeqFeature *feature, SqlInsertBuilder &insert, DbXrefInsertBuilder &dbXRefs );

	bool	loadSeqFeatureDbXRef( const QString &dbXRef, int seqFeatureId, DbXrefInsertBuilder &dbXRefs );

	int		getNextBioentryId();
	int		getNextSeqFeatureId();
	int		getTaxonId( const SeqRecord &record );
	int		getTaxonIdFromNcbiId( int ncbiId );
//	int		getDbXRefId( const QString &db, const QString &accession );
//	bool	getBioentryDbXRef( int bioID, int dbXRefId, int rank );
//	bool	getSeqFeatureDbXRef( int seqFeatureId, int dbXRefId, int rank );

//	int		addDbXRef( const QString &db, const QString &accession, int version );
//	bool	addBioentryDbXRef( int bioID, int dbXRefId, int rank );
//	bool	addSeqFeatureDbXRef( int seqFeatureId, int dbXRefId, int rank );

	void	setError( const QString &msg );

private:

	enum DataFlags {
		NoneLoaded =		0x0000,
		AnnotationsLoaded =	0x0001,
		AllLoaded =			0x0001
	};

	void	loadAnnotations();

	unsigned int			m_flags;
	int						m_bioID;
	QString					m_name, m_lastError;
	Progress				*m_progress;
	SqlServer				*m_server;
	QMap<QString, QString>	m_annotations;
};

/**
**/
SeqDatabase::SeqDatabase() : m_data( new SeqDatabaseData )
{
}

/**
**/
SeqDatabase::SeqDatabase( const SeqDatabase &db ) : m_data( db.m_data )
{
}

/**
**/
SeqDatabase::SeqDatabase( const QString &name, int bioID, SqlServer *server ) : 
	m_data( new SeqDatabaseData( name, bioID, server ) )
{
}

/**
**/
SeqDatabase::~SeqDatabase()
{
}

/**
**/
bool SeqDatabase::isValid() const
{
	return m_data->isValid();
}

/**
**/
bool SeqDatabase::load( SeqParser *parser )
{
	return m_data->load( parser );
}

/**
**/
bool SeqDatabase::load( SeqRecord &record )
{
	return m_data->load( record );
}

/**
**/
SeqRecord SeqDatabase::getRecord( int bioentryId )
{
	return m_data->getRecord( bioentryId );
}

/**
**/
SeqRecord SeqDatabase::getRecordByAccession( const QString &accession )
{
	return m_data->getRecordByAccession( accession );
}

/**
**/
QList<SeqRecord> SeqDatabase::getRecordsForTaxon( int taxonId )
{
	return m_data->getRecordsForTaxon( taxonId );
}

/**
**/
QString SeqDatabase::getAnnotation( const QString &key ) const
{
	return m_data->getAnnotation( key );
}

/**
**/
void SeqDatabase::setAnnotation( const QString &key, const QString &value )
{
	m_data->setAnnotation( key, value );
}

/**
**/
QMapIterator<QString, QString> SeqDatabase::getAnnotationIterator() const
{
	return m_data->getAnnotationIterator();
}

/**
**/
SeqDatabase& SeqDatabase::operator=( const SeqDatabase &db )
{
	m_data = db.m_data;

	return *this;
}

/**
**/
SeqDatabase::SeqDatabaseData::SeqDatabaseData() : m_bioID( -1 ), m_server( NULL ), m_progress( NULL ), m_flags( NoneLoaded )
{
}

/**
**/
SeqDatabase::SeqDatabaseData::SeqDatabaseData( const QString &name, int bioID, SqlServer *server ) : m_name( name ),
	m_bioID( bioID ), m_server( server ), m_progress( NULL ), m_flags( NoneLoaded )
{
}

/**
**/
SeqDatabase::SeqDatabaseData::~SeqDatabaseData()
{
}

/**
**/
bool SeqDatabase::SeqDatabaseData::isValid() const
{
	if( !m_server || m_bioID < 0 )
		return false;

	return true;
}

/**
**/
SeqRecord SeqDatabase::SeqDatabaseData::getRecord( int bioentryId )
{
	if( !m_server )
		return SeqRecord();

	return m_server->getRecord( bioentryId, m_bioID );
}

/**
**/
SeqRecord SeqDatabase::SeqDatabaseData::getRecordByAccession( const QString &accession )
{
	if( !m_server )
		return SeqRecord();

	return m_server->getRecordByAccession( accession, m_bioID );
}

/**
**/
QList<SeqRecord> SeqDatabase::SeqDatabaseData::getRecordsForTaxon( int taxonId )
{
	if( !m_server )
		return QList<SeqRecord>();

	return m_server->getRecordsForTaxon( taxonId, m_bioID );
}

/**
**/
QString SeqDatabase::SeqDatabaseData::getAnnotation( const QString &key ) const
{
	if( (m_flags & AnnotationsLoaded) == 0 )
		const_cast<SeqDatabaseData*>( this )->loadAnnotations();

	return m_annotations.value( key );
}

/**
**/
void SeqDatabase::SeqDatabaseData::setAnnotation( const QString &key, const QString &value )
{
	m_annotations[key] = value;
}

/**
**/
QMapIterator<QString, QString> SeqDatabase::SeqDatabaseData::getAnnotationIterator() const
{
	if( (m_flags & AnnotationsLoaded) == 0 )
		const_cast<SeqDatabaseData*>( this )->loadAnnotations();

	return QMapIterator<QString, QString>( m_annotations );
}

/**
**/
bool SeqDatabase::SeqDatabaseData::load( SeqParser *parser )
{
	if( !parser ){
		setError( "NULL pointer passed to load(SeqParser*)" );
		return false;
	}

	if( !m_server->startTransaction() )
		return false;

	bool		status = true;
	Progress	progress( "Loading records into database...", parser->getNumRecords() * 10 );

	m_progress = &progress;
	status = loadAll( parser );
	m_progress = NULL;

	if( status )
		m_server->commit();
	else
		m_server->rollback();

	progress.finish();

	return status;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::load( SeqRecord &record )
{
	if( !loadBioentryTable( record ) )
		return false;

	if( !loadBioentryDate( record ) )
		return false;

	if( !loadBiosequence( record ) )
		return false;
	
	if( !loadComment( record ) )
		return false;

	DbXrefInsertBuilder	dbXRefs( m_server );

	if( !loadDbXRefs( record, dbXRefs ) )
		return false;

	if( !loadReferences( record ) )
		return false;
	
	if( !loadAnnotations( record ) )
		return false;

	if( !loadSeqFeatures( record, dbXRefs ) )
		return false;

	dbXRefs.flush();

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadAll( SeqParser *parser )
{
	if( !parser || !m_server )
		return false;

	m_progress->status( "Inserting bioentries..." );

	if( !loadBioentryTables( parser ) )
		return false;

	if( m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting bioentry dates..." );

	if( !loadBioentryDates( parser ) )
		return false;

	if( m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting biosequences..." );

	if( !loadBiosequences( parser ) )
		return false;
	
	if( m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting comments..." );

	if( !loadComments( parser ) )
		return false;

	if( m_progress->isCanceled() )
		return false;

	DbXrefInsertBuilder	dbXRefs( m_server );

	m_progress->status( "Inserting database cross-references..." );

	if( !loadDbXRefs( parser, dbXRefs ) )
		return false;

	if( m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting references..." );

	if( !loadReferences( parser ) )
		return false;
	
	if( m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting sequence annotations..." );

	if( !loadAnnotations( parser ) )
		return false;

	if( m_progress->isCanceled() )
		return false;

	if( !loadSeqFeatures( parser, dbXRefs ) )
		return false;

	return dbXRefs.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBioentryTables( SeqParser *parser )
{
	if( !m_server )
		return false;

	if( !parser ){
		setError( "NULL pointer passed to loadBioentryTables()" );
		return false;
	}

	int	bioId = getNextBioentryId();

	if( bioId < 0 )
		return false;

	QString sql = "INSERT INTO bioentry (bioentry_id, biodatabase_id, taxon_id, name, accession, "
					"identifier, division, description, version) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	QMutableListIterator<SeqRecord>	recIt( parser->getMutableRecordIterator() );

	while( recIt.hasNext() ){
		SeqRecord	&rec = recIt.next();
		QString		vals = makeBioentryTableValues( rec, bioId );

		rec.setBioentryId( bioId++ );

		if( vals.isEmpty() )
			return false;
		if( !insert.addRow( vals ) )
			return false;
		if( m_progress )
			m_progress->step();
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBioentryTable( SeqRecord &record )
{
	if( !m_server )
		return false;

	QString sql = "INSERT INTO bioentry (biodatabase_id, taxon_id, name, accession, "
					"identifier, division, description, version) VALUES ";

	sql += makeBioentryTableValues( record );

	if( !m_server->exec( sql ) )
		return false;

	record.setBioentryId( m_server->getQuery().lastInsertId().toInt() );

	return true;
}

/**
**/
QString SeqDatabase::SeqDatabaseData::makeBioentryTableValues( const SeqRecord &record, int bioId )
{
	int	taxonId = getTaxonId( record );

	if( taxonId < 0 )
		return QString();

	QString	identifier = record.getAnnotation( c_annGenBankId );
	QString	division = record.getAnnotation( c_annDataFileDivision );
	QString	version = record.getAnnotation( c_annSequenceVersion );

	if( version.isEmpty() )
		version = "0";

	QString	vals = "(";

	if( bioId >= 0 )
		vals += QString::number( bioId ) + ", ";

	vals += QString::number( m_bioID ) + ", ";
	vals += QString::number( taxonId ) + ", ";
	vals += m_server->esc( record.getName() ) + ", ";
	vals += m_server->esc( record.getId() ) + ", ";
	vals += m_server->esc( identifier ) + ", ";
	vals += m_server->esc( division ) + ", ";
	vals += m_server->esc( record.getDescription() ) + ", ";
	vals += version + ")";

	return vals;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBioentryDates( SeqParser *parser )
{
	if( !m_server || !parser )
		return false;

	QString sql = "INSERT INTO bioentry_qualifier_value (bioentry_id, term_id, value, rank) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		if( !insert.addRow( makeBioentryDateValues( recIt.next() ) ) )
			return false;
		if( m_progress )
			m_progress->step();
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBioentryDate( const SeqRecord &record )
{
	if( !m_server )
		return false;

	QString sql = "INSERT INTO bioentry_qualifier_value (bioentry_id, term_id, value, rank) VALUES ";

	sql += makeBioentryDateValues( record );

	return m_server->exec( sql );
}

/**
**/
QString SeqDatabase::SeqDatabaseData::makeBioentryDateValues( const SeqRecord &record )
{
	if( !m_server )
		return QString();

	int		dateId = m_server->getTermId( c_termDateChanged, c_ontAnnotationTags );
	QDate	date = record.getDate();

	if( !date.isValid() )
		date = QDate::currentDate();

	QString	dateStr = "(";

	dateStr += QString::number( record.getBioentryId() ) + ", ";
	dateStr += QString::number( dateId ) + ", ";
	dateStr += m_server->esc( date.toString( "dd-MMM-yyyy" ) ) + ", 1)";

	return dateStr;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBiosequences( SeqParser *parser )
{
	if( !m_server || !parser ){
		setError( "NULL pointer passed to loadBiosequences()" );
		return false;
	}

	QString sql = "INSERT INTO biosequence (bioentry_id, version, length, seq, alphabet) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		const SeqRecord	&rec = recIt.next();
		Sequence		*seq = rec.getSequence();

		if( m_progress )
			m_progress->step();

		if( !seq )
			continue;

		if( seq->getLength() > c_largeSequenceLimit ){
			if( !loadBigBiosequence( seq, rec.getBioentryId() ) )
				return false;
		}
		else {
			if( !insert.addRow( makeBiosequenceValues( seq, rec.getBioentryId() ) ) )
				return false;
		}
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBiosequence( const SeqRecord &record )
{
	Sequence	*seq = record.getSequence();

	if( !seq )
		return true; // nothing to do

	if( seq->getLength() > c_largeSequenceLimit )
		return loadBigBiosequence( seq, record.getBioentryId() );

	QString sql = "INSERT INTO biosequence (bioentry_id, version, length, seq, alphabet) VALUES ";

	sql += makeBiosequenceValues( seq, record.getBioentryId() );

	return m_server->exec( sql );
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadBigBiosequence( Sequence *seq, int bioId )
{
	// todo:fix this!
	Core::log( "Sequence to large to load into database for bioentry " + QString::number( bioId ) );
	return true;

	if( !seq )
		return false;

	QSqlQuery	query( m_server->getDatabase() );

	query.prepare( "INSERT INTO biosequence (bioentry_id, version, length, seq, alphabet) VALUES (?, ?, ?, ?, ? )" );

	query.addBindValue( bioId );
	query.addBindValue( 0 );
	query.addBindValue( seq->getLength() );
	query.addBindValue( seq->getSequence() );

	switch( seq->getAlphabet() ){
	case Sequence::DNA:
		query.addBindValue( c_alphaDNA );
		break;
	case Sequence::RNA:
		query.addBindValue( c_alphaRNA );
		break;
	case Sequence::Protein:
		query.addBindValue( c_alphaProtein );
		break;
	default:
		query.addBindValue( c_alphaUnknown );
		break;
	};

	return query.exec();
}

/**
**/
QString SeqDatabase::SeqDatabaseData::makeBiosequenceValues( Sequence *seq, int bioId )
{
	if( !seq )
		return QString();

	QString	vals = "(";

	vals += QString::number( bioId ) + ", 0, ";
	vals += QString::number( seq->getLength() ) + ", ";
	vals += "'" + QString( seq->getSequence() ) + "', ";

	switch( seq->getAlphabet() ){
	case Sequence::DNA:
		vals += m_server->esc( c_alphaDNA );
		break;
	case Sequence::RNA:
		vals += m_server->esc( c_alphaRNA );
		break;
	case Sequence::Protein:
		vals += m_server->esc( c_alphaProtein );
		break;
	default:
		vals += m_server->esc( c_alphaUnknown );
		break;
	};

	vals += ")";

	return vals;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadComments( SeqParser *parser )
{
	if( !parser ){
		setError( "NULL pointer passed to loadComments()" );
		return false;
	}

	QString sql = "INSERT INTO comment (bioentry_id, comment_text, rank) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		QString	val = makeCommentValues( recIt.next() );

		if( m_progress )
			m_progress->step();

		if( val.isEmpty() )
			continue;

		if( !insert.addRow( val ) )
			return false;
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadComment( const SeqRecord &record )
{
	QString sql = "INSERT INTO comment (bioentry_id, comment_text, rank) VALUES ";
	QString	val = makeCommentValues( record );

	if( val.isEmpty() )
		return true;

	return m_server->exec( sql + val );
}

/**
**/
QString SeqDatabase::SeqDatabaseData::makeCommentValues( const SeqRecord &record )
{
	QString	comment = record.getComment();

	if( comment.isEmpty() )
		return QString();

	QString	vals( "(" );

	if( comment.length() > c_maxTextSize ){
		Core::warning( QString( "Comment too long for bioentry %1, it will be truncated." ).arg( record.getBioentryId() ) );
			
		comment.truncate( c_maxTextSize );
	}

	vals += QString::number( record.getBioentryId() ) + ", ";
	vals += m_server->esc( comment ) + ", 1)";

	return vals;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadDbXRefs( SeqParser *parser, DbXrefInsertBuilder &dbXRefs )
{
	if( !parser ){
		setError( "NULL pointer passed to loadDbXRefs()" );
		return false;
	}

	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		// todo: is there a faster way to do this?
		if( !loadDbXRefs( recIt.next(), dbXRefs ) )
			return false;

		if( m_progress )
			m_progress->step();
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadDbXRefs( const SeqRecord &record, DbXrefInsertBuilder &dbXRefs )
{
	int	rank = 0;
	QStringListIterator	xrefIt = record.getXRefIterator();

	while( xrefIt.hasNext() ){
		QString	xref = xrefIt.next();
		int		idx = xref.indexOf( ':' );

		if( idx < 0 )
			continue;

		QString	db = xref.left( idx );
		QString	accession = xref.mid( idx + 1 );
		int		dbXRefId = dbXRefs.getDbXRefId( db, accession );
		
		if( !dbXRefs.getBioentryDbXRef( record.getBioentryId(), dbXRefId, rank ) )
			return false;

		rank++;
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadReferences( SeqParser *parser )
{
	if( m_progress )
		m_progress->step( parser->getNumRecords() );

	// todo: implement this
	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadReferences( const SeqRecord &record )
{
	// todo: implement this
	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadAnnotations( SeqParser *parser )
{
	if( !parser ){
		setError( "NULL pointer passed to loadAnnotations()" );
		return false;
	}

	QString sql = "INSERT INTO bioentry_qualifier_value (bioentry_id, term_id, value) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		if( !loadAnnotations( recIt.next(), insert ) )
			return false;

		if( m_progress )
			m_progress->step();
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadAnnotations( const SeqRecord &record )
{
	QString sql = "INSERT INTO bioentry_qualifier_value (bioentry_id, term_id, value) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );

	if( !loadAnnotations( record, insert ) )
		return false;

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadAnnotations( const SeqRecord &record, SqlInsertBuilder &insert )
{
	if( !m_server )
		return false;

	int	ontologyId = m_server->getOntologyId( c_ontAnnotationTags );

	if( ontologyId < 0 )
		return false;

	QMapIterator<QString, QString>	annotIt = record.getAnnotationIterator();

	while( annotIt.hasNext() ){
		annotIt.next();

		int		termID = m_server->getTermId( annotIt.key(), ontologyId );

		insert.addValue( record.getBioentryId() );
		insert.addValue( termID );
		insert.addValue( annotIt.value() );

		if( !insert.addRow() )
			return false;
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatures( SeqParser *parser, DbXrefInsertBuilder &dbXRefs )
{
	if( !parser ){
		setError( "NULL pointer passed to loadSeqFeatures()" );
		return false;
	}

	m_progress->status( "Inserting sequence features..." );

	if( !loadSeqFeaturesBasic( parser ) )
		return false;

	if( m_progress && m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting sequence locations..." );

	if( !loadSeqFeaturesLocations( parser, dbXRefs ) )
		return false;

	if( m_progress && m_progress->isCanceled() )
		return false;

	m_progress->status( "Inserting sequence location qualifiers..." );

	if( !loadSeqFeaturesQualifiers( parser, dbXRefs ) )
		return false;

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatures( const SeqRecord &record, DbXrefInsertBuilder &dbXRefs )
{
	QHash<QString,int>			featureRanks;
	QListIterator<SeqFeature*>	featureIt( record.getFeatureIterator() );

	while( featureIt.hasNext() ){
		SeqFeature	*feature = featureIt.next();
		QString		type = feature->getType();
		int			featureRank = 0;

		if( featureRanks.contains( type ) )
			featureRank = featureRanks[type]++;
		else
			featureRanks[type] = featureRank + 1;

		if( !loadSeqFeatureBasic( feature, featureRank, record.getBioentryId() ) )
			return false;

		QString sql1 = "INSERT INTO location (seqfeature_id, dbxref_id, term_id, start_pos, end_pos, strand, rank) VALUES ";

		SqlInsertBuilder	insert1( sql1, m_server );

		if( !loadSeqFeatureLocations( feature, insert1, dbXRefs ) )
			return false;

		if( !insert1.flush() )
			return false;

		QString sql2 = "INSERT INTO seqfeature_qualifier_value (seqfeature_id, term_id, rank, value) VALUES ";

		SqlInsertBuilder	insert2( sql2, m_server );

		if( !loadSeqFeatureQualifiers( feature, insert2, dbXRefs ) )
			return false;

		if( !insert2.flush() )
			return false;
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeaturesBasic( SeqParser *parser )
{
	if( !parser )
		return false;

	int		seqfeatureId = getNextSeqFeatureId();
	QString sql = "INSERT INTO seqfeature (seqfeature_id, bioentry_id, type_term_id, source_term_id, rank) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );
	
	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		if( !loadSeqFeaturesBasic( recIt.next(), insert, seqfeatureId ) )
			return false;

		if( m_progress )
			m_progress->step();
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeaturesBasic( const SeqRecord &record, SqlInsertBuilder &insert, int &seqfeatureId )
{
	int	bioId = record.getBioentryId();
	int	ontologyId = m_server->getOntologyId( c_ontSeqFeatureKeys );
	int	sourceTermId = m_server->getTermId( c_termEMBL, c_ontSeqFeatureSources );

	if( ontologyId < 0 || sourceTermId < 0 )
		return false;

	QHash<QString,int>			featureRanks;
	QListIterator<SeqFeature*>	featureIt( record.getFeatureIterator() );

	while( featureIt.hasNext() ){
		SeqFeature	*feature = featureIt.next();
		QString		type = feature->getType();
		int			featureRank = 0;

		if( featureRanks.contains( type ) )
			featureRank = featureRanks[type]++;
		else
			featureRanks[type] = featureRank + 1;

		int	seqFeatureKeyId = m_server->getTermId( feature->getType(), ontologyId );

		if( seqFeatureKeyId < 0 )
			return false;

		insert.addValue( seqfeatureId );
		insert.addValue( bioId );
		insert.addValue( seqFeatureKeyId );
		insert.addValue( sourceTermId );
		insert.addValue( featureRank );

		if( !insert.addRow() )
			return false;

		feature->setSeqFeatureId( seqfeatureId++ );
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatureBasic( SeqFeature *feature, int rank, int bioID )
{
	int	seqFeatureKeyId = m_server->getTermId( feature->getType(), c_ontSeqFeatureKeys );
	int	sourceTermId = m_server->getTermId( c_termEMBL, c_ontSeqFeatureSources );

	if( seqFeatureKeyId < 0 || sourceTermId < 0 )
		return false;

	QString	sql = "INSERT INTO seqfeature (bioentry_id, type_term_id, source_term_id, rank) "
						"VALUES (";
	
	sql += QString::number( bioID ) + ", ";
	sql += QString::number( seqFeatureKeyId ) + ", ";
	sql += QString::number( sourceTermId ) + ", ";
	sql += QString::number( rank ) + ")";

	if( !m_server->exec( sql ) )
		return false;

	feature->setSeqFeatureId( m_server->getQuery().lastInsertId().toInt() );

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeaturesLocations( SeqParser *parser, DbXrefInsertBuilder &dbXRefs )
{
	if( !parser )
		return false;

	QString sql = "INSERT INTO location (seqfeature_id, dbxref_id, term_id, start_pos, end_pos, strand, rank) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );
	
	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		QListIterator<SeqFeature*>	featureIt( recIt.next().getFeatureIterator() );

		while( featureIt.hasNext() ){
			if( !loadSeqFeatureLocations( featureIt.next(), insert, dbXRefs ) )
				return false;
		}

		if( m_progress )
			m_progress->step();
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatureLocations( SeqFeature *feature, SqlInsertBuilder &insert, DbXrefInsertBuilder &dbXRefs )
{
	SeqRange	range = feature->getLocation();
	int			rank = 1;

	QListIterator<SeqLocation>	locIt( range.getLocationIterator() );

	while( locIt.hasNext() ){
		if( !loadSeqFeatureLocation( locIt.next(), insert, dbXRefs, rank, feature->getSeqFeatureId() ) )
			return false;
		rank++;
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatureLocation( const SeqLocation &loc, SqlInsertBuilder &insert, 
										DbXrefInsertBuilder &dbXRefs, int rank, int seqFeatureId )
{
	int	strand = 0;

	if( loc.getStrand() == SeqLocation::Positive )
		strand = 1;
	else if( loc.getStrand() == SeqLocation::Negative )
		strand = -1;

	QString	ref = loc.getReference();
	int		xrefId = -1;

	if( !ref.isEmpty() ){
		xrefId = dbXRefs.getDbXRefId( loc.getReferenceDb(), ref );

		if( xrefId < 0 )
			return false;

		// it's necessary to flush the dbxrefs here so that we don't
		// try to insert the location before the XRef it refers to.
		dbXRefs.flush();
	}

	insert.addValue( seqFeatureId );
	if( xrefId < 0 )
		insert.addNullValue();
	else
		insert.addValue( xrefId );
	insert.addNullValue();
	insert.addValue( loc.getStart() );
	insert.addValue( loc.getEnd() );
	insert.addValue( strand );
	insert.addValue( rank );

	return insert.addRow();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeaturesQualifiers( SeqParser *parser, DbXrefInsertBuilder &dbXRefs )
{
	if( !parser )
		return false;

	QString sql = "INSERT INTO seqfeature_qualifier_value (seqfeature_id, term_id, rank, value) VALUES ";

	SqlInsertBuilder	insert( sql, m_server );
	
	QListIterator<SeqRecord>	recIt( parser->getRecordIterator() );

	while( recIt.hasNext() ){
		QListIterator<SeqFeature*>	featureIt( recIt.next().getFeatureIterator() );

		while( featureIt.hasNext() ){
			if( !loadSeqFeatureQualifiers( featureIt.next(), insert, dbXRefs ) )
				return false;
		}

		if( m_progress ){
			m_progress->step();
			if( m_progress->isCanceled() )
				return false;
		}
	}

	return insert.flush();
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatureQualifiers( SeqFeature *feature, SqlInsertBuilder &insert, DbXrefInsertBuilder &dbXRefs )
{
	int		annotId = m_server->getOntologyId( c_ontAnnotationTags );
	int		seqFeatureId = feature->getSeqFeatureId();

	QMapIterator<QString, QString>	qualIt( feature->getQualifierIterator() );

	while( qualIt.hasNext() ){
		qualIt.next();

		QString	key = qualIt.key();

		if( key == "db_xref" ){
			if( !loadSeqFeatureDbXRef( qualIt.value(), seqFeatureId, dbXRefs ) )
				return false;
			continue;
		}

		int	termId = m_server->getTermId( key, annotId );

		if( termId < 0 )
			return false;

		QString	value = qualIt.value();

		if( value.length() > c_maxTextSize ){
			Core::warning( QString( "Sequence feature value too long for feature %1, it will be truncated." ).arg( seqFeatureId ) );
			
			value.truncate( c_maxTextSize );
		}
		
		insert.addValue( seqFeatureId );
		insert.addValue( termId );
		insert.addValue( 0 );
		insert.addValue( value );

		if( !insert.addRow() )
			return false;
	}

	return true;
}

/**
**/
bool SeqDatabase::SeqDatabaseData::loadSeqFeatureDbXRef( const QString &dbXRef, int seqFeatureId, DbXrefInsertBuilder &dbXRefs )
{
	int	idx = dbXRef.indexOf( ':' );

	if( idx < 0 )
		return false;

	QString	db = dbXRef.left( idx );
	QString	accession = dbXRef.mid( idx + 1 );
	int		dbXRefId = dbXRefs.getDbXRefId( db, accession );

	if( dbXRef < 0 )
		return false;

	if( !dbXRefs.getSeqFeatureDbXRef( seqFeatureId, dbXRefId, 0 ) )
		return false;

	return true;
}

/**
**/
int SeqDatabase::SeqDatabaseData::getNextBioentryId()
{
	QString	sql = "SELECT MAX(bioentry_id) FROM bioentry";

	if( !m_server->exec( sql ) )
		return -1;

	QSqlQuery	query = m_server->getQuery();

	if( query.first() )
		return query.value( 0 ).toInt() + 1;

	return 1;
}

/**
**/
int SeqDatabase::SeqDatabaseData::getNextSeqFeatureId()
{
	QString	sql = "SELECT MAX(seqfeature_id) FROM seqfeature";

	if( !m_server->exec( sql ) )
		return -1;

	QSqlQuery	query = m_server->getQuery();

	if( query.first() )
		return query.value( 0 ).toInt() + 1;

	return 1;
}

/**
**/
int SeqDatabase::SeqDatabaseData::getTaxonId( const SeqRecord &record )
{
	int	taxonId = record.getTaxonId();

	if( taxonId >= 0 )
		return taxonId;
		
	taxonId = record.getNcbiTaxonId();

	if( taxonId < 0 ){
		// try to find the NCBI taxon id in the source feature of the record
		QListIterator<SeqFeature*>	featureIt = record.getFeatureIterator();

		while( featureIt.hasNext() ){
			SeqFeature	*feature = featureIt.next();

			if( feature->getType() == "source" ){
				QString	qual = feature->getQualifier( c_qualDbXRef );

				if( qual.startsWith( "taxon:" ) ){
					taxonId = qual.mid( 6 ).toInt();
					break;
				}
			}
		}
	}

	if( taxonId >= 0 ){
		// convert the NCBI taxon id into a BioSQL taxon id
		int	id = getTaxonIdFromNcbiId( taxonId );

		if( id >= 0 )
			return id;
	}

	QString	scientificName = record.getAnnotation( c_annOrganism );

	if( !scientificName.isEmpty() ){
		// try to find the taxon id by scientific name
		QString	sql = "SELECT taxon_id FROM taxon_name WHERE name_class = 'scientific name' "
						"AND name = " + m_server->esc( scientificName );

		if( !m_server->exec( sql ) )
			return -1;

		QSqlQuery	query = m_server->getQuery();

		if( query.first() )
			return query.value( 0 ).toInt();
	}

	// todo: try searching on the common name

	Core::warning( QString( "Unable to find taxon ID for record '%1', setting to unidentified." ).arg( record.getId() ) );

	QString	sql = "SELECT taxon_id FROM taxon_name WHERE name = 'unidentified'";

	if( !m_server->exec( sql ) )
		return -1;

	QSqlQuery	query = m_server->getQuery();

	if( query.first() )
		return query.value( 0 ).toInt();

	setError( "Could not find taxonomy entry for 'unidentified'" );

	return -1;
}

/**
**/
int SeqDatabase::SeqDatabaseData::getTaxonIdFromNcbiId( int ncbiId )
{
	QString	sql = "SELECT taxon_id FROM taxon WHERE ncbi_taxon_id = " + QString::number( ncbiId );

	if( !m_server->exec( sql ) )
		return -1;

	QSqlQuery	query = m_server->getQuery();

	if( !query.first() )
		return -1;

	return query.value( 0 ).toInt();
}

/**
**
int SeqDatabase::SeqDatabaseData::getDbXRefId( const QString &db, const QString &accession )
{
	QString	sql = QString( "SELECT dbxref_id FROM dbxref WHERE dbname = %1 AND accession = %2" )
					.arg( m_server->esc( db ) ).arg( m_server->esc( accession ) );

	if( !m_server->exec( sql ) )
		return -1;

	QSqlQuery	&query = m_server->getQuery();

	if( query.first() )
		return query.value( 0 ).toInt();

	return addDbXRef( db, accession, 1 );
}

/**
**
bool SeqDatabase::SeqDatabaseData::getBioentryDbXRef( int bioID, int dbXRefId, int rank )
{
	QString	sql = QString( "SELECT * FROM bioentry_dbxref WHERE bioentry_id = %1 AND dbxref_id = %2" )
						.arg( bioID ).arg( dbXRefId );

	if( !m_server->exec( sql ) )
		return false;

	QSqlQuery	&query = m_server->getQuery();

	if( query.first() )
		return true;
	
	return addBioentryDbXRef( bioID, dbXRefId, rank );
}

/**
**
bool SeqDatabase::SeqDatabaseData::getSeqFeatureDbXRef( int seqFeatureId, int dbXRefId, int rank )
{
	QString		sql = QString( "SELECT * FROM seqfeature_dbxref WHERE seqfeature_id = %1 AND dbxref_id = %2" )
						.arg( seqFeatureId ).arg( dbXRefId );

	if( !m_server->exec( sql ) )
		return false;

	QSqlQuery	&query = m_server->getQuery();

	if( query.first() )
		return true;
	
	return addSeqFeatureDbXRef( seqFeatureId, dbXRefId, rank );
}

/**
**
int SeqDatabase::SeqDatabaseData::addDbXRef( const QString &db, const QString &accession, int version )
{
	QString	sql = QString( "INSERT INTO dbxref(dbname, accession, version) VALUES (%1, %2, %3)" )
					.arg( m_server->esc( db ) ).arg( m_server->esc( accession ) ).arg( version );

	if( !m_server->exec( sql ) )
		return -1;

	return m_server->getQuery().lastInsertId().toInt();
}

/**
**
bool SeqDatabase::SeqDatabaseData::addBioentryDbXRef( int bioID, int dbXRefId, int rank )
{
	QString	sql = QString( "INSERT INTO bioentry_dbxref(bioentry_id, dbxref_id, rank) VALUES (%1, %2, %3)" ).arg( bioID )
						.arg( dbXRefId ).arg( rank );

	return m_server->exec( sql );
}

/**
**
bool SeqDatabase::SeqDatabaseData::addSeqFeatureDbXRef( int seqFeatureId, int dbXRefId, int rank )
{
	QString	sql = QString( "INSERT INTO seqfeature_dbxref(seqfeature_id, dbxref_id, rank) VALUES (%1, %2, %3)" )
						.arg( seqFeatureId ).arg( dbXRefId ).arg( rank );

	return m_server->exec( sql );
}
*/

/**
**/
void SeqDatabase::SeqDatabaseData::setError( const QString &msg )
{
	Core::error( "Database error: " + msg );
	m_lastError = msg;
}

/**
**/
void SeqDatabase::SeqDatabaseData::loadAnnotations()
{
	m_flags |= AnnotationsLoaded;

	if( !m_server )
		return;

	QString	sql = "SELECT name, value FROM biodatabase_qualifier_value JOIN term USING (term_id) "
					"WHERE biodatabase_id = " + QString::number( m_bioID ) + " ORDER BY rank";

	if( !m_server->exec( sql ) )
		return;

	QSqlQuery	query = m_server->getQuery();

	if( !query.first() )
		return;

	do {
		setAnnotation( query.value( 0 ).toString(), query.value( 1 ).toString() );
	} while( query.next() );
}
